CREATE TABLE [dbo].[CategoryRequest](
	[CategoryRequestId] [int] NOT NULL,
	[BusinessId] [int] NOT NULL,
	[CategoryName] [varchar](250) NOT NULL,
 CONSTRAINT [PK_CategoryRequest] PRIMARY KEY CLUSTERED 
(
	[CategoryRequestId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CategoryRequest]  WITH CHECK ADD  CONSTRAINT [FK_CategoryRequest_Business] FOREIGN KEY([BusinessId])
REFERENCES [dbo].[Business] ([BusinessId])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[CategoryRequest] CHECK CONSTRAINT [FK_CategoryRequest_Business]
GO
ALTER PROCEDURE [dbo].[spAPI_Business_Register]
	@login varchar(50)
	,@password varchar(50)
	,@email varchar(50)
	,@businessName varchar(100)
	,@web varchar(100)
	,@businessPhone varchar(50)
	,@cellPhone varchar(50)
	,@tollFreePhone varchar(50)
	,@fax varchar(50)
	,@streetAddress varchar(100)
	,@streetAddress2 varchar(100)
	,@city varchar(50)
	,@state varchar(2)
	,@zip varchar(5)
	,@description varchar(MAX)
	,@referencedBy varchar(100)
	,@sunFrom datetime
	,@sunTo datetime
	,@sunClosed bit
	,@monFrom datetime
	,@monTo datetime
	,@monClosed bit
	,@tueFrom datetime
	,@tueTo datetime
	,@tueClosed bit
	,@wedFrom datetime
	,@wedTo datetime
	,@wedClosed bit
	,@thuFrom datetime
	,@thuTo datetime
	,@thuClosed bit
	,@friFrom datetime
	,@friTo datetime
	,@friClosed bit
	,@satFrom datetime
	,@satTo datetime
	,@satClosed bit
	,@categories varchar(max)
	,@newCategory1 varchar(250)
	,@newCategory2 varchar(250)
	,@newCategory3 varchar(250)
	,@latitude float
	,@longitude float
	,@creditCardNo varchar(20)
	,@creditCardExpDate datetime
	,@creditCardName varchar(50)
	,@creditCardPin varchar(10)
	,@useBusinessAddress bit
	,@billingStreetAddress varchar(100)
	,@billingStreetAddress2 varchar(100)
	,@billingCity varchar(50)
	,@billingState varchar(2)
	,@billingZip varchar(5)
	
	,@PictureContentType VARCHAR(50) = ''
	,@Picture varbinary(max) = null	
AS
BEGIN
	SET NOCOUNT ON
	SET XACT_ABORT ON
	
	BEGIN TRANSACTION
	
		INSERT INTO UserBusinessLogins(LoginName, LoginType)
		VALUES(@login, 'B')
		if @@error <> 0 return
	
		INSERT INTO [Business]
			([Login]
			,[Email]
			,[Password]
			,[BusinessName]
			,[Web]
			,[BusinessPhone]
			,[CellPhone]
			,[TollFreePhone]
			,[Fax]
			,[StreetAddress1]
			,[StreetAddress2]
			,[IsActive]
			,[Deleted]
			,[City]
			,[StateCode]
			,[ZipCode]
			,[Description]
			,[CreditCardNo]
			,[CreditCardExpDate]
			,[CreditCardName]
			,[CreditCardPin]
			,[BillingAddress1]
			,[BillingAddress2]
			,[BillingCity]
			,[BillingStateCode]
			,[BillingZipCode]
			,[BillingUse]
			,[ReferencedBy]
			,[Latitude]
			,[Longitude]
			,PictureContentType
			,Picture)
		 VALUES
			(@Login
			,@Email
			,@Password
			,@BusinessName
			,@Web
			,@BusinessPhone
			,@CellPhone
			,@TollFreePhone
			,@Fax
			,@StreetAddress
			,@StreetAddress2
			,1
			,0
			,@City
			,@State
			,@Zip
			,@Description
			,@CreditCardNo
			,@CreditCardExpDate
			,@CreditCardName
			,@CreditCardPin
			,@BillingStreetAddress
			,@BillingStreetAddress2
			,@BillingCity
			,@BillingState
			,@BillingZip
			,@useBusinessAddress
			,@ReferencedBy
			,@Latitude
			,@Longitude
			,@PictureContentType
			,@Picture)
		declare @businessId int = SCOPE_IDENTITY()
		
		
		INSERT INTO BusinessHours(BusinessId, [WeekDay], FromTime, ToTime, Closed)
		values
			(@BusinessId, 'Sun', @SunFrom, @SunTo, @SunClosed)
			,(@BusinessId, 'Mon', @MonFrom, @MonTo, @MonClosed)
			,(@BusinessId, 'Tue', @TueFrom, @TueTo, @TueClosed)
			,(@BusinessId, 'Wed', @WedFrom, @WedTo, @WedClosed)
			,(@BusinessId, 'Thu', @ThuFrom, @ThuTo, @ThuClosed)
			,(@BusinessId, 'Fri', @FriFrom, @FriTo, @FriClosed)
			,(@BusinessId, 'Sat', @SatFrom, @SatTo, @SatClosed)
    
    
		INSERT INTO BusinessCategory(BusinessId, CategoryId)
		SELECT @BusinessId, CONVERT(INT, Items.Item)
		FROM dbo.fnAPI_String_Split(@Categories, ',') AS Items
		
		IF (@newCategory1 IS NOT NULL)
			IF (@newCategory1 <> '')
				INSERT INTO CategoryRequest
				(BusinessId, CategoryName)
				VALUES (@BusinessId, @newCategory1)
		IF (@newCategory2 IS NOT NULL)
			IF (@newCategory2 <> '')
				INSERT INTO CategoryRequest
				(BusinessId, CategoryName)
				VALUES (@BusinessId, @newCategory2)
		IF (@newCategory3 IS NOT NULL)
			IF (@newCategory3 <> '')
				INSERT INTO CategoryRequest
				(BusinessId, CategoryName)
				VALUES (@BusinessId, @newCategory3)
						
		declare @SecurityToken uniqueidentifier = NEWID()
					
		INSERT INTO UserToken(SecurityToken, LoginName, ExpirationDate)
		VALUES(@SecurityToken, @Login, '99991231')
		
		SELECT @SecurityToken AS SecurityToken

	COMMIT TRANSACTION
END
GO
CREATE PROCEDURE spCategoryRequest_Delete
(
	@CategoryRequestId INT
)
AS
BEGIN
	SET NOCOUNT ON

	DELETE 
	FROM CategoryRequest
	WHERE (CategoryRequestId = @CategoryRequestId)
END
GO
CREATE PROCEDURE spCategoryRequest_Confirm
(
	@CategoryRequestId INT,
	@ParentId INT
)
AS
BEGIN
	SET NOCOUNT ON
	SET XACT_ABORT ON
	
	BEGIN TRANSACTION
		INSERT INTO Category
		(CategoryName, ParentId)
		SELECT CategoryName, @ParentId
		FROM CategoryRequest
		WHERE (CategoryRequestId = @CategoryRequestId)
		
		DELETE 
		FROM CategoryRequest
		WHERE (CategoryRequestId = @CategoryRequestId)
	COMMIT TRANSACTION
END	
GO

